﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.IO;
using Citi.PTSConsolidation.Domain;
using Citi.PTSConsolidation.Domain.Utils;
using ClosedXML.Excel;

namespace Citi.PTSConsolidation.ConsoleUI.Report
{
   

    class Program
    {
        static string PtsConnectionString { get { return ConfigurationManager.AppSettings["PtsConnectionString"]; } }
        static string ErrorFile { get { return ConfigurationManager.AppSettings["ErrorFile"]; } }
        static string PtsReportSavePath { get { return ConfigurationManager.AppSettings["PtsReportSavePath"]; } }
        static string PtsReportTempPath { get { return ConfigurationManager.AppSettings["PtsReportTempPath"]; } }
        
        public static void Main(string[] args)
        {
            

            return;
            try
            {
                GenerateReport(DateTime.Today.Year);
                GenerateReport(DateTime.Today.Year + 1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                File.WriteAllText(ErrorFile, e.ToString());
                throw e;
            }
        }

        private static void GenerateReport(int year)
        {
            Console.WriteLine("Brazil PTS Report {0}: Generation has started at {1}", year, DateTime.Now);
            GenerateBrazilPtsReport(string.Format("BookOfWork{0}", year));
            Console.WriteLine("Brazil PTS Report {0}: Generation has finished at {1}", year, DateTime.Now);
        }

        private static void GenerateBrazilPtsReport(string tableName)
        {
            GC.Collect();

            var stringBuilder = new StringBuilder("select ");
            stringBuilder.Append("PeriodYear as [Period Year],");
            stringBuilder.Append("AccountLevel1 as [Account Level 1],");
            stringBuilder.Append("AccountLevel2 as [Account Level 2],");
            stringBuilder.Append("AccountLevel3 as [Account Level 3],");
            stringBuilder.Append("InitiativeId as [Initiative Id],");
            stringBuilder.Append("InitiativeName as [Initiative Name],");
            stringBuilder.Append("ProgramId as [Program Id],");
            stringBuilder.Append("ProgramName as [Program Name],");
            stringBuilder.Append("ProjectId as [Project Id],");
            stringBuilder.Append("ProjectName as [Project Name],");
            stringBuilder.Append("ProgramManager as [Program Manager],");
            stringBuilder.Append("ProgramStartDate as [Program Start Date],");
            stringBuilder.Append("ProgramEndDate as [Program End Date],");
            stringBuilder.Append("ProjectStatus as [Project Status],");
            stringBuilder.Append("ProjectStartDate as [Project Start Date],");
            stringBuilder.Append("ProjectEndDate as [Project End Date],");
            stringBuilder.Append("ProjectManager as [Project Manager],");
            stringBuilder.Append("BillingClientGocCode as [Billing Client Goc Code],");
            stringBuilder.Append("Client3Name as [Client 3 Name],");
            stringBuilder.Append("Client4Name as [Client 4 Name],");
            stringBuilder.Append("Client5Name as [Client 5 Name],");
            stringBuilder.Append("Client6Name as [Client 6 Name],");
            stringBuilder.Append("Client7Name as [Client 7 Name],");
            stringBuilder.Append("Client8Name as [Client 8 Name],");
            stringBuilder.Append("Client9Name as [Client 9 Name],");
            stringBuilder.Append("Client10Name as [Client 10 Name],");
            stringBuilder.Append("ClientGocCode as [Client Goc Code],");
            stringBuilder.Append("ClientGOC.ManagedGeography as [Client Geography Code],");
            stringBuilder.Append("ClientGOC.ManagedGeographyDescription as [Client Geography Description],");
            stringBuilder.Append("System5Name as [System 5 Name],");
            stringBuilder.Append("System6Name as [System 6 Name],");
            stringBuilder.Append("System7Name as [System 7 Name],");
            stringBuilder.Append("System8Name as [System 8 Name],");
            stringBuilder.Append("System9Name as [System 9 Name],");
            stringBuilder.Append("System10Name as [System 10 Name],");
            stringBuilder.Append("SystemGOCCode as [System Goc Code],");
            stringBuilder.Append("SystemGOC.ManagedGeography as [System Geography Code],");
            stringBuilder.Append("SystemGOC.ManagedGeographyDescription as [System Geography Description],");
            stringBuilder.Append("SystemRegion as [System Region],");
            stringBuilder.Append("LOB as [LOB],");
            stringBuilder.Append("RuntheBankBuildtheBank as [Run the Bank Build the Bank],");
            stringBuilder.Append("ProgramProjectLinkIndicator as [Program Project Link Indicator],");
            stringBuilder.Append("WorkTypeFinancials as [Work Type Financials],");
            stringBuilder.Append("InitiativeInvestimentCategory as [Init Invest Category],");
            stringBuilder.Append("ProgramInvestimentCategory as [Prog Invest Category],");
            stringBuilder.Append("ProgramCapitalizedIndicator as [Prog Capind],");
            stringBuilder.Append("ProgramStatus as [Program Status],");
            stringBuilder.Append("ProjectCapitalization as [Project Capitalization],");
            stringBuilder.Append("BowFlag as [Bow Flag],");
            stringBuilder.Append("BowFlag2 as [Bow Flag2],");
            stringBuilder.Append("ProjectType as [Project Type],");
            stringBuilder.Append("ProgramCarryoverIndicator as [Program Carryover Indicator],");
            stringBuilder.Append("TaxonomyName as [Taxonomy Name],");
            stringBuilder.Append("CURate as [Cu Rate],");
            stringBuilder.Append("FirstOfProgramDesc as [First of Program Desc],");
            stringBuilder.Append("LobParent as [Lob Parent],");
            stringBuilder.Append("AdjustedFlag as [Adjusted Flag],");
            stringBuilder.Append("January as [January Bow],");
            stringBuilder.Append("February as [February Bow],");
            stringBuilder.Append("March as [March Bow],");
            stringBuilder.Append("April as [April Bow],");
            stringBuilder.Append("May as [May Bow],");
            stringBuilder.Append("June as [June Bow],");
            stringBuilder.Append("July as [July Bow],");
            stringBuilder.Append("August as [August Bow],");
            stringBuilder.Append("September as [September Bow],");
            stringBuilder.Append("October as [October Bow],");
            stringBuilder.Append("November as [November Bow],");
            stringBuilder.Append("December as [December Bow],");
            stringBuilder.Append("YearToDate as [Year To date Bow],");
            stringBuilder.Append("FullYear as [Full Year Bow]");
            stringBuilder.Append(" from ").Append(tableName).Append(" left join GOC as ClientGOC on ClientGOC.GocAttributes = ClientGocCode");
            stringBuilder.Append(" left join GOC as SystemGOC on SystemGOC.GocAttributes = SystemGocCode");
            stringBuilder.Append(" Where (ClientGOC.ManagedGeography = 'BR' OR SystemGOC.ManagedGeography = 'BR') AND ");
            stringBuilder.Append(tableName).Append(".ReferenceDate = '").Append(DateTime.Today.ToString("yyyy-MM-dd")).Append("'");

            var dataTable = SqlHelper.ExecuteReader(stringBuilder.ToString(), PtsConnectionString);
            GC.Collect();
            
            GenerateExcelFromDataTable(dataTable, tableName);
        }

        private static void GenerateExcelFromDataTable(DataTable dataTable, string fileName)
        {
            var pathToSave = string.Format(@"{0}\{1}_{2}.xlsx", PtsReportTempPath, fileName, DateTime.Today.ToString("yyyyMMdd"));

            if (File.Exists(pathToSave))
                File.Delete(pathToSave);

          //  ExportToXLSX(pathToSave, dataTable, "Brazil Projects");

          //      return;

            var workbook = new XLWorkbook();
            workbook.Worksheets.Add(dataTable, "Brazil Projects");
            workbook.Style.Font.FontSize = 11;
            workbook.SaveAs(pathToSave);

            Helpers.FileHelper.Compress(pathToSave, PtsReportSavePath);
        }

        public static void ExportToXLSX(string sheetToCreate, DataTable dtToExport, string tableName)
        {
            List<DataRow> rows = new List<DataRow>();
            foreach (DataRow row in dtToExport.Rows) rows.Add(row);
            ExportToXLSX(sheetToCreate, rows, dtToExport, tableName);
        }

        public static void ExportToXLSX(string sheetToCreate, List<DataRow> selectedRows, DataTable origDataTable, string tableName)
        {
            char Space = ' ';

            if (tableName == null)
                tableName = string.Empty;

            tableName = tableName.Trim().Replace(Space, '_');
            if (tableName.Length == 0)
                tableName = origDataTable.TableName.Replace(Space, '_');

            if (tableName.Length == 0)
                tableName = "NoTableName";

            if (tableName.Length > 30)
                tableName = tableName.Substring(0, 30);

            //Excel names are less than 31 chars
            string queryCreateExcelTable = string.Concat("CREATE TABLE [" , tableName , "] (");
            Dictionary<string, string> colNames = new Dictionary<string, string>();

            foreach (DataColumn dc in origDataTable.Columns)
            {
                //Cause the query to name each of the columns to be created.
                string modifiedcolName = dc.ColumnName;//.Replace(Space, '_').Replace('.', '#');
                string origColName = dc.ColumnName;
                colNames.Add(modifiedcolName, origColName);

                queryCreateExcelTable += string.Concat( "[" , modifiedcolName , "]" , " text,");

            }

            queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";

            //adds the closing parentheses to the query string
            if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
            {
                //use Excel 2007 for large sheets.
                sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
            }

            string strCn = string.Empty;
            string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower();
            if (ext == ".xls") strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'";
            if (ext == ".xlsx") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
            if (ext == ".xlsb") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' ";
            if (ext == ".xlsm") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' ";

            var cn = new System.Data.OleDb.OleDbConnection(strCn);
            var cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn);
            cn.Open();
            cmd.ExecuteNonQuery();
            var da = new System.Data.OleDb.OleDbDataAdapter(string.Concat("SELECT * FROM [" , tableName , "]"), cn);
            var cb = new System.Data.OleDb.OleDbCommandBuilder(da);

            //creates the INSERT INTO command
            cb.QuotePrefix = "[";
            cb.QuoteSuffix = "]";
            cmd = cb.GetInsertCommand();

            //gets a hold of the INSERT INTO command.
            foreach (DataRow row in selectedRows)
            {
                foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
                    param.Value = row[colNames[param.SourceColumn.Replace('#', '.')]];

                cmd.ExecuteNonQuery(); //INSERT INTO command.
            }
            cn.Close();
            cn.Dispose();
            da.Dispose();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    }
}
